package com.gxuwz.medical.domain.family;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.database.DbUtil11;
import com.gxuwz.medical.domain.house.House;
import com.gxuwz.medical.exception.UserNotFoundException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 家庭档案管理
 * 
 * @author Administrator
 * 
 */
public class Family {

	private String familycode; // 家庭编号
	private String areacode; // 区域编号
	private String sumfamily; // 家庭人口数
	private String sumfarmer; // 农业人口数
	private String address; // 家庭住址
	private String createtime; // 创建时间
	private String registrar; // 记录员
	private String housemaster; // 户主姓名
	private String housecode;
	private String xareacode;
	private String zareacode;
	private String careacode;
	private String group;

	public String getFamilycode() {
		return familycode;
	}

	public void setFamilycode(String familycode) {
		this.familycode = familycode;
	}

	public String getAreacode() {
		return areacode;
	}

	public void setAreacode(String areacode) {
		this.areacode = areacode;
	}

	public String getSumfamily() {
		return sumfamily;
	}

	public void setSumfamily(String sumfamily) {
		this.sumfamily = sumfamily;
	}

	public String getSumfarmer() {
		return sumfarmer;
	}

	public void setSumfarmer(String sumfarmer) {
		this.sumfarmer = sumfarmer;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getCreatetime() {
		return createtime;
	}

	public void setCreatetime(String createtime) {
		this.createtime = createtime;
	}

	public String getRegistrar() {
		return registrar;
	}

	public void setRegistrar(String registrar) {
		this.registrar = registrar;
	}

	public String getHousemaster() {
		return housemaster;
	}

	public void setHousemaster(String housemaster) {
		this.housemaster = housemaster;
	}



	public String getHousecode() {
		return housecode;
	}

	public void setHousecode(String housecode) {
		this.housecode = housecode;
	}

	public String getXareacode() {
		return xareacode;
	}

	public void setXareacode(String xareacode) {
		this.xareacode = xareacode;
	}

	public String getZareacode() {
		return zareacode;
	}

	public void setZareacode(String zareacode) {
		this.zareacode = zareacode;
	}

	public String getCareacode() {
		return careacode;
	}

	public void setCareacode(String careacode) {
		this.careacode = careacode;
	}

	public String getGroup() {
		return group;
	}

	public void setGroup(String group) {
		this.group = group;
	}

	public Family(String familycode) throws UserNotFoundException {

		load(familycode);
	}

	public Family(String xareacode,String zareacode,String careacode,String housecode,String familycode,String housemaster,String sumfamily,String sumfarmer,String address,String createtime,String registrar,String areacode,String group) {
		super();
		this.familycode = familycode;
		this.areacode = areacode;
		this.sumfamily = sumfamily;
		this.sumfarmer = sumfarmer;
		this.address = address;
		this.createtime = createtime;
		this.registrar = registrar;
		this.housemaster = housemaster;
		this.housecode = housecode;
		this.xareacode = xareacode;
		this.zareacode = zareacode;
		this.careacode = careacode;
		this.group = group;
	}

	public Family() {
	}
	
	/**
	 * 家庭人口数自增
	 */
	public void countNumber(String farmer)throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt=null;
		try{
		   conn=DbUtil.getConn();
		   String sql="update t_family set sumfamily=sumfamily+1,sumfarmer=sumfarmer+"+farmer+" where familycode=?";
		   pstmt=conn.prepareStatement(sql);
		   pstmt.setString(1, this.familycode);
		   int count=pstmt.executeUpdate();
		}catch(SQLException se){
			se.printStackTrace();
			throw se;
		}finally{
			DbUtil.close(pstmt);
		}
	}
	

	public void addFamily(String familycode, String areacode, String sumfamily,
			String sumfarmer, String address, String createtime,
			String registrar, String housemaster) throws SQLException {
		this.familycode = familycode;
		this.areacode = areacode;
		this.sumfamily =sumfamily ;
		this.sumfarmer = sumfarmer;
		this.address = address;
		this.createtime = createtime;
		this.registrar = registrar;
		this.housemaster = housemaster;
		Connection conn = null;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			saveToDB(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	public void editFamily(String familycode, String areacode,
			String sumfamily, String sumfarmer, String address,
			String createtime, String registrar, String housemaster)
			throws SQLException {
		this.familycode = familycode;
		this.areacode = areacode;
		this.sumfamily = sumfamily;
		this.sumfarmer = sumfarmer;
		this.address = address;
		this.createtime = createtime;
		this.registrar = registrar;
		this.housemaster = housemaster;
		Connection conn = null;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			editToDB(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	protected void editToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuff = new StringBuffer(
					"update t_family set areacode=?,sumfamily=?,sumfarmer=?,address=?,createtime=?,registrar=?,housemaster=? where familycode=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, this.areacode);
			pstmt.setString(2, this.sumfamily);
			pstmt.setString(3, this.sumfarmer);
			pstmt.setString(4, this.address);
			pstmt.setString(5, this.createtime);
			pstmt.setString(6, this.registrar);
			pstmt.setString(7, this.housemaster);
			pstmt.setString(8, this.familycode);

			pstmt.executeUpdate();

		} catch (SQLException e) {
			throw new SQLException("修改t_family表时出错！");
		} finally {
			DbUtil.close(pstmt);
		}

	}

	protected void saveToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		String sumfamily = "1";
		String sumfarmer = "1";
		try {
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("insert into t_family(familycode,areacode,sumfamily,sumfarmer,address,createtime,registrar,housemaster)values(?,?,?,?,?,?,?,?)");
			String sql = sqlBuffer.toString();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, this.familycode);
			pstmt.setString(2, this.areacode);
			pstmt.setString(3, this.sumfamily);
			pstmt.setString(4, this.sumfarmer);
			pstmt.setString(5, this.address);
			pstmt.setString(6, this.createtime);
			pstmt.setString(7, this.registrar);
			pstmt.setString(8, this.housemaster);

			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
	}

	public void load(String familycode) throws UserNotFoundException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			pstmt = conn
					.prepareStatement("select * from t_family where familycode=?");
			int index = 1;
			pstmt.setString(index, familycode);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				this.familycode = rs.getString("familycode");
				this.areacode = rs.getString("areacode");
				this.sumfamily = rs.getString("sumfamily");
				this.sumfarmer = rs.getString("sumfarmer");
				this.address = rs.getString("address");
				this.createtime = rs.getString("createtime");
				this.registrar = rs.getString("registrar");
				this.housemaster = rs.getString("housemaster");
				this.housecode = rs.getString("housecode");
				this.xareacode = rs.getString("xareacode");
				this.zareacode = rs.getString("zareacode");
				this.careacode = rs.getString("careacode");
				this.group = rs.getString("group");
			}

		} catch (SQLException e) {
			throw new UserNotFoundException("Family with id " + familycode
					+ " could not be loaded from the database.");
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	public void delFamily(String familycode) throws SQLException {
		this.familycode = familycode;
		Connection conn = null;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			delForDB(conn);
			// 删除关联户主信息
			unbindHouse(conn);
			unbindMember(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	protected void delForDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("delete from t_family where familycode=?");
			pstmt = conn.prepareStatement(sqlBuffer.toString());
			pstmt.setString(1, this.familycode);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("删除t_familycode时出错！",e);
		} finally {
			DbUtil.close(pstmt);
		}
	}

	private void unbindHouse(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuff = new StringBuffer();
			sqlBuff.append("delete from t_house where familycode=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, familycode);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("删除与t_family关联的t_house表时出错！",e);
		} finally {
			DbUtil.close(pstmt);
		}
	}
	
	private void unbindMember(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuff = new StringBuffer();
			sqlBuff.append("delete from t_member where familycode=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, familycode);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("删除与t_family关联的t_member表时出错！",e);
		} finally {
			DbUtil.close(pstmt);
		}
	}

	
	public void saveFamily(String areacode, String grade, String housemaster,
			String address, String createtime, String registrar, String idcard,
			String sex, String birthday, String nation, String health,
			String culture, String relationship, String farmer,
			String marriage, String work, String phone)
			throws SQLException {
			Connection conn = null;
			this.areacode = areacode;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			this.familycode = CreateFamilycode();		
			this.sumfamily = "1";
			this.sumfarmer = "1";
			this.housecode = this.familycode+"01";
			int i = saveToFamily(familycode,areacode, grade, housemaster,
					 address, createtime, registrar);
			House house = new House();
			house.addHouse(housemaster, familycode, idcard, sex, birthday, nation, health, culture, relationship, farmer, marriage, work, phone, housecode);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}
	
	private String CreateFamilycode() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select max(familycode) from t_family where familycode like'"+this.areacode+"%' ";
		try {
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			Integer number=1;
			String maxcode = "";
			System.out.println("maxcode:"+maxcode);
			if (rs.next()) {
				maxcode = rs.getString(1);
			}
			if (maxcode != null) {
				int beginIndex = maxcode.length() - 4;
				String no = maxcode.substring(beginIndex);
				number = Integer.parseInt(no);
				++number;
				// 使用0补足位数
				no = String.format("%04d", number);
				maxcode = this.areacode + no;
			} else {
				String no = String.format("%04d", number);
				maxcode = this.areacode + no;
			}
//			System.out.println("maxcode1:"+maxcode);
			this.familycode = maxcode;
			return maxcode;
		} catch (SQLException e) {
			throw new SQLException("创建父节点ID" + this.familycode + "失败!", e);
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	private int saveToFamily(String familycode,String areacode, String grade, String housemaster, String address, String createtime, String registrar)
			throws SQLException {

		int index = -1;
		int index1 = -1;
		int grade1 = Integer.parseInt(grade);
		String xareacode = null; // 县级编码
		String zareacode = null; // 乡镇编码
		String careacode = null; // 村编码
		String group  = areacode   + "0001";
		if (grade1 == 1) {
			xareacode = areacode;
		} else if (grade1 == 2) {
			xareacode = areacode.substring(0, 8);
			zareacode = areacode;
		} else if (grade1 == 3) {
			xareacode = areacode.substring(0, 8);
			zareacode = areacode.substring(0, 10);
			careacode = areacode;
		} else if (grade1 == 4) {
			xareacode = areacode.substring(0, 8);
			zareacode = areacode.substring(0, 10);
			careacode = areacode.substring(0, 12);
		}
//		String housecode = this.housecode + familycode; 	
		try {

			String sql1 = "insert into t_family(familycode,areacode,sumfamily,sumfarmer,address,"
					+ "createtime,registrar,housemaster,xareacode,zareacode,careacode,housecode)"
					+ "value(?,?,?,?,?,?,?,?,?,?,?,?)";

			index1 = DbUtil11.executeUpdate(sql1, familycode, areacode,
					sumfamily, sumfarmer, address, createtime, registrar,
					housemaster, xareacode, zareacode, careacode, housecode);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (index1 > 0) {
			return index = 1;
		} else {
			return index = -1;
		}
	}
	
	public static List<Family> getNewsList(String where) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
//		System.out.println("where:" + where);

		List<Family> newslist = new ArrayList<Family>();

		conn = DbUtil.getConn();

		String sql = "select * from t_family where " + where;
		pstmt = conn.prepareStatement(sql);
//		System.out.println("sql:" + sql);
		ResultSet rs = pstmt.executeQuery(sql);
		try {
			while (rs.next()) {
				Family news = new Family();
				String familycode = rs.getString("familycode");
				String areacode = rs.getString("areacode");
				String sumfamily = rs.getString("sumfamily");
				String sumfarmer = rs.getString("sumfarmer");
				String address = rs.getString("address");
				String createtime = rs.getString("createtime");
				String registrar = rs.getString("registrar");
				String housemaster = rs.getString("housemaster");
				String housecode = rs.getString("housecode");
				String xareacode = rs.getString("xareacode");
				String zareacode = rs.getString("zareacode");
				String careacode = rs.getString("careacode");
				String group = rs.getString("group");
				
				news.setFamilycode(familycode);
				news.setAreacode(areacode);
				news.setSumfamily(sumfamily);
				news.setSumfarmer(sumfarmer);
				news.setAddress(address);
				news.setCreatetime(createtime);
				news.setRegistrar(registrar);
				news.setHousemaster(housemaster);
				news.setHousecode(housecode);
				news.setXareacode(xareacode);
				news.setZareacode(zareacode);
				news.setCareacode(careacode);
				news.setGroup(group);
		
				newslist.add(news);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return newslist;
	}
	
}
